"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : 功能测试
Case Name   : 会话1开启事务select into结合for update给表加锁，会话2删除表数据，合理报错
Create At   : 2022/11/21
@zou_jialiang0501208402
Description :
            1、设置并发更新同一行时单个锁的最长等待时间为3000
            2、创建表1,在表中插入数据
            3、会话1中开启事务使用for update锁查询并新建表
            4、新会话删除表数据
            5、会话1提交事务
            6、新会话删除表数据
            7、查询会话1新建表
            8、清理环境
Expect      :
            1、成功
            2、成功
            3、成功
            4、失败
            5、成功
            6、成功
            7、成功
            8、成功
History     :
    Modified by @yangyixiang 2023/2/16: 优化用例清理环境正确恢复参数默认值
"""

import os
import time
import unittest
from yat.test import Node
from yat.test import macro

from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.ComThread import ComThread


class SQL(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.Constant = Constant()
        self.pri_node = Node('PrimaryDbUser')
        self.env_path = macro.DB_ENV_PATH
        self.db_path = macro.DB_INSTANCE_PATH
        self.t_name1 = 't_select_into_0089_1'
        self.t_name2 = 't_select_into_0089_2'
        self.config_lockwait = 'update_lockwait_timeout'

    def test_lock(self):
        step1 = '--step1:设置并发更新同一行时单个锁的最长等待时间为3000;expect:成功--'
        self.log.info(step1)
        msg = self.primary_sh.execute_gsguc('reload',
                                            self.Constant.GSGUC_SUCCESS_MSG,
                                            f'{self.config_lockwait} = 3000')
        self.assertTrue(msg, '执行失败' + step1)

        step2 = '--step2:创建表1,在表中插入数据;expect:成功--'
        self.log.info(step2)
        sql_cmd = f'''drop table if exists {self.t_name1};
                     create table {self.t_name1} (id int,c_clob varchar(10));
                     insert into {self.t_name1} values(1,'a');
                     insert into {self.t_name1} values(1,'b');
                     insert into {self.t_name1} values(1,'c');
                     insert into {self.t_name1} values(1,'d');
                  '''
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn(self.Constant.TABLE_DROP_SUCCESS, result,
                      '执行失败' + step2)
        self.assertIn(self.Constant.CREATE_TABLE_SUCCESS, result,
                      "执行失败" + step2)
        self.assertEqual(4, result.count(self.Constant.INSERT_SUCCESS_MSG),
                         '执行失败' + step2)

        step3 = '--step3:会话1中开启事务使用for update锁查询并新建表;expect:成功--'
        self.log.info(step3)
        sql_cmd = f"begin;" \
                  f"select * into {self.t_name2} from {self.t_name1} " \
                  f"for update of {self.t_name1};" \
                  f"select pg_sleep(20);" \
                  f"commit;"
        session1 = ComThread(self.primary_sh.execut_db_sql, args=(sql_cmd,))
        session1.setDaemon(True)
        session1.start()
        time.sleep(5)

        step4 = '--step4:新会话删除表数据;expect:失败--'
        self.log.info(step4)
        sql_cmd = f'delete from {self.t_name1};'
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn('ERROR:  Lock wait timeout', result,
                      '执行失败' + step4)

        step5 = '--step5:会话1提交事务;expect:成功--'
        self.log.info(step5)
        session1.join()
        session1_result = session1.get_result()
        self.log.info(session1_result)
        self.assertIn(self.Constant.COMMIT_SUCCESS_MSG, session1_result,
                      '执行失败' + step5)

        step6 = '--step6:新会话删除表数据;expect:成功--'
        self.log.info(step6)
        sql_cmd = f'delete from {self.t_name1};'
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn(self.Constant.DELETE_SUCCESS_MSG, result,
                      '执行失败' + step6)

        step7 = '--step7:查询会话1新建表;expect:成功--'
        self.log.info(step7)
        sql_cmd = f'select * from {self.t_name2};'
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn('4 rows', result, '执行失败' + step7)

    def tearDown(self):
        step8 = '--step8:清理环境;expect:成功--'
        self.log.info(step8)
        sql_cmd = f"drop table if exists {self.t_name1};" \
                  f"drop table if exists {self.t_name2};"
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertEqual(2, result.count(self.Constant.TABLE_DROP_SUCCESS),
                         '执行失败' + step8)
        msg = self.primary_sh.execute_gsguc('reload',
                                            self.Constant.GSGUC_SUCCESS_MSG,
                                        f'{self.config_lockwait} = 120000')
        self.assertTrue(msg, '执行失败' + step8)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')